DBMS vendors incorporate complex parallel processing architectures to manage dynamic and constantly changing workloads, growing volume of data, increasing number of users, and implementation of the new applications. Leading DBMS products that support parallelism include Oracle 10g with GRID architecture, IBM's DB2 UDB ESE and NCR's Teradata with Massively Parallel Processing (MPP) architecture. Performance management of the concurrent Data Loading, Business Intelligence (BI) Decision Support (DSS) and Online Transaction Processing (OLTP) workloads in a parallel processing environment is difficult and brings many challenges.
In a balanced parallel processing environment, the queries on many nodes are processed in parallel much faster. Change in a pattern of data access can significantly increase frequency of accessing specific data through one of the nodes and affect the balance of resource utilization. Data skewness, index and materialized views strategy, physical and virtual configuration, and selection of the OS and DBMS parameters can also affect the balance in usage of resources and performance of the applications in parallel processing environments.
In an unbalanced environment, the response time of requests depends on the contention for the over-utilized devices. Queuing time for resources on the over-utilized node can slow down the response time for other queries.
Wrong performance management and database tuning, priority allocation and concurrency control decisions can negatively affect the balance of utilization and level of service, customer satisfaction and business competitiveness. A DBA may spend significant amounts of time finding and fixing the problems. The time to identify and fix the problem could take days or even weeks. During this period the response time could be slow and reduced system throughput can negatively affect the business.
To improve DBA productivity DBMS vendors often offer wizards and self healing solutions. IBM refers to such solutions as “autonomic computing”, Oracle calls it “self-healing” and NCR refers to this as “Teradata Active Systems Management” (TASM). A DBA typically should provide a set of SQL as input to the Wizard. Wizards recommend creation of new indexes and materialized views and estimate savings in CPU time and number of I/O operations only for the set of analyzed SQL. These recommendations have several limitations. If the DBA sends all SQL processed by DBMS to a wizard, it could take a very long time to perform an analysis. If DBA sends only SQL captured during short measurement interval, results could be inaccurate.
Recommendations based on analysis of the critical SQL collected during short period of time do not guarantee that they address the continuous and most serious problems.
There is a risk that recommendation is just a reaction based on occasional event.
Another risk is that the implementation of recommendations can improve performance for the selected set of SQL, but potentially can negatively affect other SQL and workloads.
DBMS wizard provide recommendations without setting the expectations about the expected outcome of their implementation on response time and throughput for each of the workloads. As a result, after implementation of the wizard's recommendations a DBA may not know if the goal is achieved or not. The DBA cannot compare multiple options and find the most effective solution, thus satisfying Service Level Objectives (SLOs) for different workloads.